package preparedStatement;

import bean.Customer;
import bean.Order;
import org.junit.Test;
import util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;

/*
 * 针对Order表的通用查询
 * */
public class OrderForQuery {

    //简单的查询order表操作
    @Test
    public void testQuery() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            connection = JDBCUtils.getConnection();
            //预编译SQL语句，返回preparedStatement的实例
            String sql = "SELECT order_id,order_name,order_date FROM `order` WHERE order_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            preparedStatement.setInt(1, 1);
            //执行，并获取结果集
            resultSet = preparedStatement.executeQuery();
            //处理结果集
            if (resultSet.next()) {
                int orderId = resultSet.getInt(1);
                String orderName = resultSet.getString(2);
                Date orderDate = resultSet.getDate(3);

                Order order = new Order(orderId, orderName, orderDate);
                System.out.println(order.toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }

    }


    /*
    * 针对表的字段名与类名的属性不一致情况：
    * 1.必须声明sql时，使用类的属性名来命名字段名
    * 2.使用ResultSetMetaData时，需要使用getColumnLabel() 来替换getColumnName() 获取列的别名
    * 说明：如果sql没有给字段起别名，getColumnLabel()就是获取列名，所有推荐使用它
    **/
    @Test
    public void testQuery2() {
        //对字段名起别名，来对应对象类的属性名
        String sqlOrder = "SELECT order_id orderId,order_name orderName,order_date orderDate FROM `order` WHERE order_id = ?";
        Order order = orderForQuery(sqlOrder, 1);
        System.out.println(order.toString());
    }
    //通用的查询order表的操作
    public Order orderForQuery(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //执行并获取结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集中列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            //处理数据
            if (resultSet.next()) {
                Order order = new Order();
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i + 1);//获取列的值
                    //获取表的别名getColumnLabel()  获取表的列名getColumnName()
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //给order对象指定的某个属性，赋值为value。(通过反射)
                    Field field = Order.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);//允许访问私有属性
                    field.set(order, value);
                }
                return order;
            }
        } catch (SQLException | IllegalAccessException | NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }
        return null;

    }
}
